/**
 * 
 */
package goodroommate.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;

import goodroommate.dal.DatabaseSession;
import goodroommate.dao.IPostingDao;
import goodroommate.domain.Posting;
import goodroommate.domain.University;
import goodroommate.exception.DatabaseException;

/**
 * @author ZhouWuAi
 *
 */
public class PostingDao implements IPostingDao {

	/* (non-Javadoc)
	 * @see goodroommate.dao.IPostingDao#addPosting(goodroommate.dal.DatabaseSession, goodroommate.domain.Posting)
	 */
	@Override
	public boolean addPosting(DatabaseSession dbs, Posting posting) throws DatabaseException {
		
		String sql="INSERT INTO postings (PostMan,Topic,Type,Content,Domain) VALUES(?,?,?,?,?)";
		try{
			dbs.executeUpdate(sql, posting.getPostman(),posting.getTopic(),posting.getType(),posting.getContent(),posting.getDomain());
			return true;
			
		}catch(DatabaseException e){
			e.printStackTrace();
			throw new DatabaseException(e);
		}
	}

	@Override
	public LinkedList<Posting> getPosting(DatabaseSession dbs, String type, String topic, int postman, String domain ,int limitstart,int limitlength)
			throws DatabaseException {
		
		String sql="SELECT postings.*,users.Username FROM postings,users WHERE postings.PostMan=users.ID ";
		LinkedList<Posting> rst=new LinkedList<Posting>();
		LinkedList<Object> params=new LinkedList<Object>();
		//sql+="WHERE TRUE ";
		if(type!=null){
			sql+="AND Type LIKE ? ";
			params.add("%"+type+"%");
		}
		if(topic!=null&&(!"".equals(topic))){
			sql+="AND Topic LIKE ? ";
			params.add("%"+topic+"%");	
		}
		if(postman!=0){
			sql+="AND PostMan=? ";
			params.add(postman);
		}
		if(domain!=null){
			sql+="AND Domain LIKE ? ";
			params.add("%"+domain+"%");	
		}
		if(limitlength!=0){
			sql+="LIMIT ?,? ";
			params.add(limitstart);
			params.add(limitlength);
		}
		try{
			ResultSet rs=dbs.executeQuery(sql, params);
			while(rs.next()){
				Posting posting=new Posting();
				posting.setPostingid(rs.getInt("PostingID"));
				posting.setPostman(rs.getInt("PostMan"));
				posting.setPostmanname(rs.getString("Username"));
				posting.setType(rs.getString("Type"));
				posting.setTopic(rs.getString("Topic"));
				posting.setContent(rs.getString("Content"));
				posting.setTime(rs.getTimestamp("Time"));
				posting.setDomain(rs.getString("Domain"));
				rst.add(posting);
			}
			return rst;
		}catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException(e);
		}
	}

	@Override
	public Posting getPostingById(DatabaseSession dbs, int postingid) throws DatabaseException {
		String sql="SELECT * FROM postings WHERE PostingID=? ";
		Posting posting=new Posting();
		try{
			ResultSet rs=dbs.executeQuery(sql, postingid);
			if(rs.next()){
				posting.setPostingid(rs.getInt("PostingID"));
				posting.setPostman(rs.getInt("PostMan"));
				posting.setType(rs.getString("Type"));
				posting.setTopic(rs.getString("Topic"));
				posting.setContent(rs.getString("Content"));
				posting.setTime(rs.getTimestamp("Time"));
				posting.setDomain(rs.getString("Domain"));
				return posting;
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException(e);
		}
		return null;
	}

	@Override
	public ArrayList<University> getPostingsByUniversity(int count, DatabaseSession dbs) throws DatabaseException {
		
		String sql="SELECT universities.*, "
				+ "(SELECT COUNT(*) "
	            +"FROM postings "
	            + "WHERE universities.`Name` = postings.Domain) "
	            + "AS postingscount "
	            + "FROM universities "
				+ "ORDER BY postingscount DESC LIMIT 0," + count;
		ArrayList<University>rst=new ArrayList<University>();
		try{
			ResultSet rs=dbs.executeQuery(sql);
			while(rs.next()){
				University university=new University();
				university.setId(rs.getInt("ID"));
				university.setName(rs.getString("Name"));
				university.setMinistry(rs.getString("Ministry"));
				university.setLocation(rs.getString("Location"));
				university.setLevel(rs.getString("Level"));
				university.setInfo(rs.getString("Info"));
				university.setPostingcount(rs.getInt("postingscount"));
				rst.add(university);
			}
			return rst;
		}catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException(e);
		}
	}
	

}
